{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## The Task\n",
    "\n",
    "Do an Exploratory Data Analysis on the redcard dataset. Keeping in mind the question is the following: **Are soccer referees more likely to give red cards to dark-skin-toned players than light-skin-toned players?**\n",
    "\n",
    "- Before plotting/joining/doing something, have a question or hypothesis that you want to investigate\n",
    "- Draw a plot of what you want to see on paper to sketch the idea\n",
    "- Write it down, then make the plan on how to get there\n",
    "- How do you know you aren't fooling yourself\n",
    "- What else can I check if this is actually true?\n",
    "- What evidence could there be that it's wrong?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "%config InlineBackend.figure_format='retina'\n",
    "\n",
    "from __future__ import absolute_import, division, print_function\n",
    "import matplotlib as mpl\n",
    "from matplotlib import pyplot as plt\n",
    "from matplotlib.pyplot import GridSpec\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os, sys\n",
    "from tqdm import tqdm\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "sns.set_context(\"poster\", font_scale=1.3)\n",
    "\n",
    "import missingno as msno\n",
    "import pandas_profiling\n",
    "\n",
    "from sklearn.datasets import make_blobs\n",
    "import time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## About the Data\n",
    "\n",
    "> The dataset is available as a list with 146,028 dyads of players and referees and includes details from players, details from referees and details regarding the interactions of player-referees. A summary of the variables of interest can be seen below. A detailed description of all variables included can be seen in the README file on the project website. \n",
    "\n",
    "> From a company for sports statistics, we obtained data and profile photos from all soccer players (N = 2,053) playing in the first male divisions of England, Germany, France and Spain in the 2012-2013 season and all referees (N = 3,147) that these players played under in their professional career (see Figure 1). We created a dataset of playerâreferee dyads including the number of matches players and referees encountered each other and our dependent variable, the number of red cards given to a player by a particular referee throughout all matches the two encountered each other.\n",
    "\n",
    "> -- https://docs.google.com/document/d/1uCF5wmbcL90qvrk_J27fWAvDcDNrO9o_APkicwRkOKc/edit\n",
    "\n",
    "\n",
    "| Variable Name: | Variable Description: | \n",
    "| -- | -- | \n",
    "| playerShort | short player ID | \n",
    "| player | player name | \n",
    "| club | player club | \n",
    "| leagueCountry | country of player club (England, Germany, France, and Spain) | \n",
    "| height | player height (in cm) | \n",
    "| weight | player weight (in kg) | \n",
    "| position | player position | \n",
    "| games | number of games in the player-referee dyad | \n",
    "| goals | number of goals in the player-referee dyad | \n",
    "| yellowCards | number of yellow cards player received from the referee | \n",
    "| yellowReds | number of yellow-red cards player received from the referee | \n",
    "| redCards | number of red cards player received from the referee | \n",
    "| photoID | ID of player photo (if available) | \n",
    "| rater1 | skin rating of photo by rater 1 | \n",
    "| rater2 | skin rating of photo by rater 2 | \n",
    "| refNum | unique referee ID number (referee name removed for anonymizing purposes) | \n",
    "| refCountry | unique referee country ID number | \n",
    "| meanIAT | mean implicit bias score (using the race IAT) for referee country | \n",
    "| nIAT | sample size for race IAT in that particular country | \n",
    "| seIAT | standard error for mean estimate of race IAT   | \n",
    "| meanExp | mean explicit bias score (using a racial thermometer task) for referee country | \n",
    "| nExp | sample size for explicit bias in that particular country | \n",
    "| seExp |  standard error for mean estimate of explicit bias measure | \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def save_subgroup(dataframe, g_index, subgroup_name, prefix='raw_'):\n",
    "    save_subgroup_filename = \"\".join([prefix, subgroup_name, \".csv.gz\"])\n",
    "    dataframe.to_csv(save_subgroup_filename, compression='gzip', encoding='UTF-8')\n",
    "    test_df = pd.read_csv(save_subgroup_filename, compression='gzip', index_col=g_index, encoding='UTF-8')\n",
    "    # Test that we recover what we send in\n",
    "    if dataframe.equals(test_df):\n",
    "        print(\"Test-passed: we recover the equivalent subgroup dataframe.\")\n",
    "    else:\n",
    "        print(\"Warning -- equivalence test!!! Double-check.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def load_subgroup(filename, index_col=[0]):\n",
    "    return pd.read_csv(filename, compression='gzip', index_col=index_col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tidy Dyads and Starting Joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "clean_players = load_subgroup(\"cleaned_players.csv.gz\")\n",
    "players = load_subgroup(\"raw_players.csv.gz\", )\n",
    "countries = load_subgroup(\"raw_countries.csv.gz\")\n",
    "referees = load_subgroup(\"raw_referees.csv.gz\")\n",
    "agg_dyads = pd.read_csv(\"raw_dyads.csv.gz\", compression='gzip', index_col=[0, 1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>yellowCards</th>\n",
       "      <th>yellowReds</th>\n",
       "      <th>victories</th>\n",
       "      <th>ties</th>\n",
       "      <th>games</th>\n",
       "      <th>defeats</th>\n",
       "      <th>goals</th>\n",
       "      <th>redCards</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>refNum</th>\n",
       "      <th>playerShort</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>lucas-wilchez</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>john-utaka</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">3</th>\n",
       "      <th>abdon-prats</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pablo-mari</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ruben-pena</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">4</th>\n",
       "      <th>aaron-hughes</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aleksandar-kolarov</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>alexander-tettey</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>anders-lindegaard</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>andreas-beck</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           yellowCards  yellowReds  victories  ties  games  \\\n",
       "refNum playerShort                                                           \n",
       "1      lucas-wilchez                 0           0          0     0      1   \n",
       "2      john-utaka                    1           0          0     0      1   \n",
       "3      abdon-prats                   1           0          0     1      1   \n",
       "       pablo-mari                    0           0          1     0      1   \n",
       "       ruben-pena                    0           0          1     0      1   \n",
       "4      aaron-hughes                  0           0          0     0      1   \n",
       "       aleksandar-kolarov            0           0          1     0      1   \n",
       "       alexander-tettey              0           0          0     0      1   \n",
       "       anders-lindegaard             0           0          0     1      1   \n",
       "       andreas-beck                  0           0          1     0      1   \n",
       "\n",
       "                           defeats  goals  redCards  \n",
       "refNum playerShort                                   \n",
       "1      lucas-wilchez             1      0         0  \n",
       "2      john-utaka                1      0         0  \n",
       "3      abdon-prats               0      0         0  \n",
       "       pablo-mari                0      0         0  \n",
       "       ruben-pena                0      0         0  \n",
       "4      aaron-hughes              1      0         0  \n",
       "       aleksandar-kolarov        0      0         0  \n",
       "       alexander-tettey          1      0         0  \n",
       "       anders-lindegaard         0      0         0  \n",
       "       andreas-beck              0      0         0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_dyads.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Test if the number of games is equal to the victories + ties + defeats in the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all(agg_dyads['games'] == agg_dyads.victories + agg_dyads.ties + agg_dyads.defeats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Sanity check passes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "146028"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(agg_dyads.reset_index().set_index('playerShort'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "agg_dyads['totalRedCards'] = agg_dyads['yellowReds'] + agg_dyads['redCards']\n",
    "agg_dyads.rename(columns={'redCards': 'strictRedCards'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>yellowCards</th>\n",
       "      <th>yellowReds</th>\n",
       "      <th>victories</th>\n",
       "      <th>ties</th>\n",
       "      <th>games</th>\n",
       "      <th>defeats</th>\n",
       "      <th>goals</th>\n",
       "      <th>strictRedCards</th>\n",
       "      <th>totalRedCards</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>refNum</th>\n",
       "      <th>playerShort</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>lucas-wilchez</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>john-utaka</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">3</th>\n",
       "      <th>abdon-prats</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pablo-mari</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ruben-pena</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      yellowCards  yellowReds  victories  ties  games  \\\n",
       "refNum playerShort                                                      \n",
       "1      lucas-wilchez            0           0          0     0      1   \n",
       "2      john-utaka               1           0          0     0      1   \n",
       "3      abdon-prats              1           0          0     1      1   \n",
       "       pablo-mari               0           0          1     0      1   \n",
       "       ruben-pena               0           0          1     0      1   \n",
       "\n",
       "                      defeats  goals  strictRedCards  totalRedCards  \n",
       "refNum playerShort                                                   \n",
       "1      lucas-wilchez        1      0               0              0  \n",
       "2      john-utaka           1      0               0              0  \n",
       "3      abdon-prats          0      0               0              0  \n",
       "       pablo-mari           0      0               0              0  \n",
       "       ruben-pena           0      0               0              0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_dyads.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove records that come from players who don't have a skintone rating\n",
    "\n",
    "There are a couple of ways to do this -- set operations and joins are two ways demonstrated below: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>skintone</th>\n",
       "      <th>position_agg</th>\n",
       "      <th>weightclass</th>\n",
       "      <th>heightclass</th>\n",
       "      <th>skintoneclass</th>\n",
       "      <th>age_years</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>playerShort</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>aaron-hughes</th>\n",
       "      <td>182.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Defense</td>\n",
       "      <td>low_weight</td>\n",
       "      <td>mid_height</td>\n",
       "      <td>[0, 0.125]</td>\n",
       "      <td>33.149897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aaron-hunt</th>\n",
       "      <td>183.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Forward</td>\n",
       "      <td>low_weight</td>\n",
       "      <td>mid_height</td>\n",
       "      <td>[0, 0.125]</td>\n",
       "      <td>26.327173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aaron-lennon</th>\n",
       "      <td>165.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>0.250</td>\n",
       "      <td>Midfield</td>\n",
       "      <td>vlow_weight</td>\n",
       "      <td>vlow_height</td>\n",
       "      <td>(0.125, 0.25]</td>\n",
       "      <td>25.713895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aaron-ramsey</th>\n",
       "      <td>178.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>Midfield</td>\n",
       "      <td>mid_weight</td>\n",
       "      <td>low_height</td>\n",
       "      <td>[0, 0.125]</td>\n",
       "      <td>22.017796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>abdelhamid-el-kaoutari</th>\n",
       "      <td>180.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>0.250</td>\n",
       "      <td>Defense</td>\n",
       "      <td>low_weight</td>\n",
       "      <td>low_height</td>\n",
       "      <td>(0.125, 0.25]</td>\n",
       "      <td>22.795346</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        height  weight  skintone position_agg  weightclass  \\\n",
       "playerShort                                                                  \n",
       "aaron-hughes             182.0    71.0     0.125      Defense   low_weight   \n",
       "aaron-hunt               183.0    73.0     0.125      Forward   low_weight   \n",
       "aaron-lennon             165.0    63.0     0.250     Midfield  vlow_weight   \n",
       "aaron-ramsey             178.0    76.0     0.000     Midfield   mid_weight   \n",
       "abdelhamid-el-kaoutari   180.0    73.0     0.250      Defense   low_weight   \n",
       "\n",
       "                        heightclass  skintoneclass  age_years  \n",
       "playerShort                                                    \n",
       "aaron-hughes             mid_height     [0, 0.125]  33.149897  \n",
       "aaron-hunt               mid_height     [0, 0.125]  26.327173  \n",
       "aaron-lennon            vlow_height  (0.125, 0.25]  25.713895  \n",
       "aaron-ramsey             low_height     [0, 0.125]  22.017796  \n",
       "abdelhamid-el-kaoutari   low_height  (0.125, 0.25]  22.795346  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_players.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>yellowCards</th>\n",
       "      <th>yellowReds</th>\n",
       "      <th>victories</th>\n",
       "      <th>ties</th>\n",
       "      <th>games</th>\n",
       "      <th>defeats</th>\n",
       "      <th>goals</th>\n",
       "      <th>strictRedCards</th>\n",
       "      <th>totalRedCards</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>refNum</th>\n",
       "      <th>playerShort</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>lucas-wilchez</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>john-utaka</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">3</th>\n",
       "      <th>abdon-prats</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pablo-mari</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ruben-pena</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      yellowCards  yellowReds  victories  ties  games  \\\n",
       "refNum playerShort                                                      \n",
       "1      lucas-wilchez            0           0          0     0      1   \n",
       "2      john-utaka               1           0          0     0      1   \n",
       "3      abdon-prats              1           0          0     1      1   \n",
       "       pablo-mari               0           0          1     0      1   \n",
       "       ruben-pena               0           0          1     0      1   \n",
       "\n",
       "                      defeats  goals  strictRedCards  totalRedCards  \n",
       "refNum playerShort                                                   \n",
       "1      lucas-wilchez        1      0               0              0  \n",
       "2      john-utaka           1      0               0              0  \n",
       "3      abdon-prats          0      0               0              0  \n",
       "       pablo-mari           0      0               0              0  \n",
       "       ruben-pena           0      0               0              0  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_dyads.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>refNum</th>\n",
       "      <th>playerShort</th>\n",
       "      <th>yellowCards</th>\n",
       "      <th>yellowReds</th>\n",
       "      <th>victories</th>\n",
       "      <th>ties</th>\n",
       "      <th>games</th>\n",
       "      <th>defeats</th>\n",
       "      <th>goals</th>\n",
       "      <th>strictRedCards</th>\n",
       "      <th>totalRedCards</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>lucas-wilchez</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>john-utaka</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>abdon-prats</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>pablo-mari</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>ruben-pena</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   refNum    playerShort  yellowCards  yellowReds  victories  ties  games  \\\n",
       "0       1  lucas-wilchez            0           0          0     0      1   \n",
       "1       2     john-utaka            1           0          0     0      1   \n",
       "2       3    abdon-prats            1           0          0     1      1   \n",
       "3       3     pablo-mari            0           0          1     0      1   \n",
       "4       3     ruben-pena            0           0          1     0      1   \n",
       "\n",
       "   defeats  goals  strictRedCards  totalRedCards  \n",
       "0        1      0               0              0  \n",
       "1        1      0               0              0  \n",
       "2        0      0               0              0  \n",
       "3        0      0               0              0  \n",
       "4        0      0               0              0  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_dyads.reset_index().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>refNum</th>\n",
       "      <th>yellowCards</th>\n",
       "      <th>yellowReds</th>\n",
       "      <th>victories</th>\n",
       "      <th>ties</th>\n",
       "      <th>games</th>\n",
       "      <th>defeats</th>\n",
       "      <th>goals</th>\n",
       "      <th>strictRedCards</th>\n",
       "      <th>totalRedCards</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>playerShort</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>lucas-wilchez</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>john-utaka</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>abdon-prats</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pablo-mari</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ruben-pena</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               refNum  yellowCards  yellowReds  victories  ties  games  \\\n",
       "playerShort                                                              \n",
       "lucas-wilchez       1            0           0          0     0      1   \n",
       "john-utaka          2            1           0          0     0      1   \n",
       "abdon-prats         3            1           0          0     1      1   \n",
       "pablo-mari          3            0           0          1     0      1   \n",
       "ruben-pena          3            0           0          1     0      1   \n",
       "\n",
       "               defeats  goals  strictRedCards  totalRedCards  \n",
       "playerShort                                                   \n",
       "lucas-wilchez        1      0               0              0  \n",
       "john-utaka           1      0               0              0  \n",
       "abdon-prats          0      0               0              0  \n",
       "pablo-mari           0      0               0              0  \n",
       "ruben-pena           0      0               0              0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_dyads.reset_index().set_index('playerShort').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "player_dyad = (clean_players.merge(agg_dyads.reset_index().set_index('playerShort'),\n",
    "                                   left_index=True,\n",
    "                                   right_index=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>skintone</th>\n",
       "      <th>position_agg</th>\n",
       "      <th>weightclass</th>\n",
       "      <th>heightclass</th>\n",
       "      <th>skintoneclass</th>\n",
       "      <th>age_years</th>\n",
       "      <th>refNum</th>\n",
       "      <th>yellowCards</th>\n",
       "      <th>yellowReds</th>\n",
       "      <th>victories</th>\n",
       "      <th>ties</th>\n",
       "      <th>games</th>\n",
       "      <th>defeats</th>\n",
       "      <th>goals</th>\n",
       "      <th>strictRedCards</th>\n",
       "      <th>totalRedCards</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>playerShort</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>aaron-hughes</th>\n",
       "      <td>182.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Defense</td>\n",
       "      <td>low_weight</td>\n",
       "      <td>mid_height</td>\n",
       "      <td>[0, 0.125]</td>\n",
       "      <td>33.149897</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aaron-hughes</th>\n",
       "      <td>182.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Defense</td>\n",
       "      <td>low_weight</td>\n",
       "      <td>mid_height</td>\n",
       "      <td>[0, 0.125]</td>\n",
       "      <td>33.149897</td>\n",
       "      <td>66</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aaron-hughes</th>\n",
       "      <td>182.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Defense</td>\n",
       "      <td>low_weight</td>\n",
       "      <td>mid_height</td>\n",
       "      <td>[0, 0.125]</td>\n",
       "      <td>33.149897</td>\n",
       "      <td>77</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>8</td>\n",
       "      <td>26</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aaron-hughes</th>\n",
       "      <td>182.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Defense</td>\n",
       "      <td>low_weight</td>\n",
       "      <td>mid_height</td>\n",
       "      <td>[0, 0.125]</td>\n",
       "      <td>33.149897</td>\n",
       "      <td>163</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aaron-hughes</th>\n",
       "      <td>182.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>0.125</td>\n",
       "      <td>Defense</td>\n",
       "      <td>low_weight</td>\n",
       "      <td>mid_height</td>\n",
       "      <td>[0, 0.125]</td>\n",
       "      <td>33.149897</td>\n",
       "      <td>194</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>16</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              height  weight  skintone position_agg weightclass heightclass  \\\n",
       "playerShort                                                                   \n",
       "aaron-hughes   182.0    71.0     0.125      Defense  low_weight  mid_height   \n",
       "aaron-hughes   182.0    71.0     0.125      Defense  low_weight  mid_height   \n",
       "aaron-hughes   182.0    71.0     0.125      Defense  low_weight  mid_height   \n",
       "aaron-hughes   182.0    71.0     0.125      Defense  low_weight  mid_height   \n",
       "aaron-hughes   182.0    71.0     0.125      Defense  low_weight  mid_height   \n",
       "\n",
       "             skintoneclass  age_years  refNum  yellowCards  yellowReds  \\\n",
       "playerShort                                                              \n",
       "aaron-hughes    [0, 0.125]  33.149897       4            0           0   \n",
       "aaron-hughes    [0, 0.125]  33.149897      66            0           0   \n",
       "aaron-hughes    [0, 0.125]  33.149897      77            0           0   \n",
       "aaron-hughes    [0, 0.125]  33.149897     163            0           0   \n",
       "aaron-hughes    [0, 0.125]  33.149897     194            2           0   \n",
       "\n",
       "              victories  ties  games  defeats  goals  strictRedCards  \\\n",
       "playerShort                                                            \n",
       "aaron-hughes          0     0      1        1      0               0   \n",
       "aaron-hughes          1     0      1        0      0               0   \n",
       "aaron-hughes         13     8     26        5      0               0   \n",
       "aaron-hughes          1     1      2        0      0               0   \n",
       "aaron-hughes          3     5     16        8      0               0   \n",
       "\n",
       "              totalRedCards  \n",
       "playerShort                  \n",
       "aaron-hughes              0  \n",
       "aaron-hughes              0  \n",
       "aaron-hughes              0  \n",
       "aaron-hughes              0  \n",
       "aaron-hughes              0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_dyad.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "clean_dyads = (agg_dyads.reset_index()[agg_dyads.reset_index()\n",
    "                                   .playerShort\n",
    "                                   .isin(set(clean_players.index))\n",
    "                                  ]).set_index(['refNum', 'playerShort'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>yellowCards</th>\n",
       "      <th>yellowReds</th>\n",
       "      <th>victories</th>\n",
       "      <th>ties</th>\n",
       "      <th>games</th>\n",
       "      <th>defeats</th>\n",
       "      <th>goals</th>\n",
       "      <th>strictRedCards</th>\n",
       "      <th>totalRedCards</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>refNum</th>\n",
       "      <th>playerShort</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>lucas-wilchez</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>john-utaka</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">4</th>\n",
       "      <th>aaron-hughes</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aleksandar-kolarov</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>alexander-tettey</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           yellowCards  yellowReds  victories  ties  games  \\\n",
       "refNum playerShort                                                           \n",
       "1      lucas-wilchez                 0           0          0     0      1   \n",
       "2      john-utaka                    1           0          0     0      1   \n",
       "4      aaron-hughes                  0           0          0     0      1   \n",
       "       aleksandar-kolarov            0           0          1     0      1   \n",
       "       alexander-tettey              0           0          0     0      1   \n",
       "\n",
       "                           defeats  goals  strictRedCards  totalRedCards  \n",
       "refNum playerShort                                                        \n",
       "1      lucas-wilchez             1      0               0              0  \n",
       "2      john-utaka                1      0               0              0  \n",
       "4      aaron-hughes              1      0               0              0  \n",
       "       aleksandar-kolarov        0      0               0              0  \n",
       "       alexander-tettey          1      0               0              0  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_dyads.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((124621, 9), (146028, 9), (124621, 18))"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_dyads.shape, agg_dyads.shape, player_dyad.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Disaggregate\n",
    "\n",
    "The dyads are currently an aggregated metric summarizing all times a particular referee-player pair play were matched. To properly handle the data, we have to disaggregate the data into a tidy/long format. This means that each game is a row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# inspired by https://github.com/mathewzilla/redcard/blob/master/Crowdstorming_visualisation.ipynb\n",
    "colnames = ['games', 'totalRedCards']\n",
    "j = 0\n",
    "out = [0 for _ in range(sum(clean_dyads['games']))]\n",
    "\n",
    "for index, row in clean_dyads.reset_index().iterrows():\n",
    "    n = row['games']\n",
    "    d = row['totalRedCards']\n",
    "    ref = row['refNum']\n",
    "    player = row['playerShort']\n",
    "    for _ in range(n):\n",
    "        row['totalRedCards'] = 1 if (d-_) > 0 else 0\n",
    "        rowlist=list([ref, player, row['totalRedCards']])\n",
    "        out[j] = rowlist\n",
    "        j += 1\n",
    "\n",
    "tidy_dyads = pd.DataFrame(out, columns=['refNum', 'playerShort', 'redcard'],).set_index(['refNum', 'playerShort'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3092"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 3092\n",
    "tidy_dyads.redcard.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "373067"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Notice this is longer than before\n",
    "clean_dyads.games.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(373067, 1)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tidy_dyads.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Ok, this is a bit crazy... tear it apart and figure out what each piece is doing if it's not clear\n",
    "clean_referees = (referees.reset_index()[referees.reset_index()\n",
    "                                                 .refNum.isin(tidy_dyads.reset_index().refNum\n",
    "                                                                                       .unique())\n",
    "                                        ]).set_index('refNum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((2978, 1), (3147, 1))"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_referees.shape, referees.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "clean_countries = (countries.reset_index()[countries.reset_index()\n",
    "                                           .refCountry\n",
    "                                           .isin(clean_referees.refCountry\n",
    "                                                 .unique())\n",
    "                                          ].set_index('refCountry'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((160, 7), (161, 7))"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_countries.shape, countries.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>redcard</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>refNum</th>\n",
       "      <th>playerShort</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <th>lucas-wilchez</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <th>john-utaka</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">4</th>\n",
       "      <th>aaron-hughes</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aleksandar-kolarov</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>alexander-tettey</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           redcard\n",
       "refNum playerShort                \n",
       "1      lucas-wilchez             0\n",
       "2      john-utaka                0\n",
       "4      aaron-hughes              0\n",
       "       aleksandar-kolarov        0\n",
       "       alexander-tettey          0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tidy_dyads.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tidy_dyads.to_csv(\"cleaned_dyads.csv.gz\", compression='gzip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(373067, 1)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tidy_dyads.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
